package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Member;
import interfaces.IMember;

public class CMember implements IMember {
  private CConnection db;

  public CMember(CConnection db) {
	this.db = db;
  }
	
  @Override
  public boolean create(Member member) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("INSERT INTO Member VALUES (" + member.getUserID() + ", '" + member.getCountry() + "', '" + member.getName() + "', '"
            + member.getJoin_year() + "', '" + member.getPdaID() + "', " + member.getWorking_region() + ")");
    return i > 0;
  }

  @Override
  public boolean update(Member member) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("UPDATE Member SET Country = '" + member.getCountry() + "', Name = '"
            + member.getName() + "', Joined_Year = '" + member.getJoin_year() + "', PdaID = '"
            + member.getPdaID() + "', Working_Region_ID = " + member.getWorking_region() + " WHERE UserID = " + member.getUserID());
    return i > 0;
  }
  
  @Override
  public boolean delete(Member member) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("DELETE FROM Member WHERE UserID = " + member.getUserID());
    return i > 0;
  }

  @Override
  public ArrayList<Member> retrieveByID(int userID) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    ResultSet res = s.executeQuery("select * from member where userid = " + userID);

    ArrayList<Member> members = new ArrayList<Member>();
    
    while(res.next()) {
      Member m = new Member();
      m.setUserID(res.getInt("userID"));
      m.setCountry(res.getString("Country"));
      m.setJoin_year(res.getDate("Joined_Year"));
      m.setName(res.getString("Name"));
      m.setPdaID(res.getString("PdaID"));
      m.setWorking_region(res.getInt("Working_Region_ID"));
      
      members.add(m);
    }

    return members;
  }

  @Override
  public ArrayList<Member> retrieveAll() throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    ResultSet res = s.executeQuery("select * from member");
		
    ArrayList<Member> members = new ArrayList<Member>();
    
    while(res.next()) {
      Member m = new Member();
      m.setUserID(res.getInt("userID"));
      m.setCountry(res.getString("Country"));
      m.setJoin_year(res.getDate("Joined_Year"));
      m.setName(res.getString("Name"));
      m.setPdaID(res.getString("PdaID"));
      m.setWorking_region(res.getInt("Working_Region_ID"));        
      
      members.add(m);
    }
    
    return members;
  }
}